package cn.wp.common.core.dao;

import cn.hutool.core.annotation.AnnotationUtil;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.bean.copier.CopyOptions;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import cn.wp.common.core.WpProperties;
import cn.wp.common.core.WpVar;
import cn.wp.common.core.dao.annotation.Column;
import cn.wp.common.core.dao.annotation.Table;
import cn.wp.common.core.exception.WpDaoException;
import cn.wp.common.core.model.WpPage;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.support.TransactionTemplate;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;

/**
 * JDBC 操作封装
 * Created by chenyb on 2019/09/28
 */
@Component
@Slf4j
public class JdbcSupportTemplate {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private TransactionTemplate transactionTemplate;

    @Autowired
    private WpProperties wpProperties;

    /**
     * 根据实体获取表名
     * @return
     */
    private <T> String getTableName(Class<?> clazz){
        String tableName = clazz.getSimpleName();
        Table table = AnnotationUtil.getAnnotation(clazz, Table.class);
        if(table != null){
            tableName = table.value();
        }
        return tableName;
    }

    /**
     * 根据实体获取表名
     * @return
     */
    private <T> String[] getKeys(Class<?> clazz){
        Table table = AnnotationUtil.getAnnotation(clazz, Table.class);
        String[] keys = new String[]{WpVar.ID};
        if(table != null){
            keys = table.keys();
        }
        return keys;
    }

    /**
     * 根据实体获取表名
     * @return
     */
    private <T> List<Object> getKeyValues(String[] keys,T entity){
       List<Object> values = CollectionUtil.newArrayList();
        for (String key : keys) {
            Field field = ReflectUtil.getField(entity.getClass(),key);
            if(field == null){
                field = ReflectUtil.getField(entity.getClass(), StrUtil.toCamelCase(key));
            }
            if(field == null){
                throw new WpDaoException("key: "+ key + " not found field at " + entity.getClass());
            }
            field.setAccessible(true);
            values.add(ReflectUtil.getFieldValue(entity,field));
        }
        return values;
    }

    /**
     * 日志输出
     */
    private void jdbcLog(String sql,Object parameter){
        if(wpProperties.isDebugMode()) {
            Console.log("[ JDBC Execute ]");
            Console.log("jdbc.SqlScript = " + sql);
            Console.log("jdbc.Parameter = " + JSON.toJSONString(parameter));
            Console.log("\n");
        }
    }

    /**
     * 添加
     * @param entity
     * @param <T>
     * @return
     */
    public <T> int insert(T entity) {
        try {
            String tableName = getTableName(entity.getClass());
            Field[] fields = ReflectUtil.getFields(entity.getClass());
            StringBuilder builder = new StringBuilder();
            StringBuilder columns = new StringBuilder();
            StringBuilder placeholder = new StringBuilder();
            List<Object> values = CollectionUtil.newArrayList();

            int index = 0;
            for (Field field : fields) {
                field.setAccessible(true);
                String columnName = field.getName();
                Column column = AnnotationUtil.getAnnotation(field, Column.class);
                if (column != null) {
                    columnName = column.ignore() ? null : (column.value() == null ? columnName : column.value());
                }

                if (columnName != null) {
                    if (index > 0) {
                        columns.append(",");
                        placeholder.append(",");
                    }
                    columns.append(columnName);
                    placeholder.append("?");
                    values.add(field.get(entity));
                    index++;
                }
            }

            builder.append("insert into " + tableName);
            builder.append(" (").append(columns).append(") ");
            builder.append(" values ");
            builder.append(" (").append(placeholder).append(") ");
            jdbcLog(builder.toString(), values);
            return jdbcTemplate.update(builder.toString(), values.toArray());
        } catch (Exception ex) {
            throw new WpDaoException("数据保存出错", ex);
        }

    }
        public int insert(Object entity, String tableName,Class clazz) {
            try {
                Field[] fields = ReflectUtil.getFields(clazz);
                StringBuilder builder = new StringBuilder();
                StringBuilder columns = new StringBuilder();
                StringBuilder placeholder = new StringBuilder();
                List<Object> values = CollectionUtil.newArrayList();

                int index = 0;
                for (Field field : fields) {
                    field.setAccessible(true);
                    String columnName = field.getName();
                    Column column = AnnotationUtil.getAnnotation(field, Column.class);
                    if (column != null) {
                        columnName = column.ignore() ? null : (column.value() == null ? columnName : column.value());
                    }

                    if (columnName != null && field.get(entity)!=null) {
                        if (index > 0) {
                            columns.append(",");
                            placeholder.append(",");
                        }
                        columns.append(columnName);
                        placeholder.append("?");
                        values.add(field.get(entity));
                        index++;
                    }
                }

                builder.append("insert into " + tableName);
                builder.append(" (").append(columns).append(") ");
                builder.append(" values ");
                builder.append(" (").append(placeholder).append(") ");
                jdbcLog(builder.toString(), values);
                return jdbcTemplate.update(builder.toString(), values.toArray());
            } catch (Exception ex) {
                throw new WpDaoException("数据保存出错", ex);
            }

        }
        /*// 事务模板 不可与@Transactional同时使用
        transactionTemplate.execute(new TransactionCallbackWithoutResult() {
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                try {
                    //doSomthing;
                    int s = jdbcTemplate.update("insert into t_demo values(?,?)","123","3457788");
                    int ss = jdbcTemplate.update("insert into t_demo3 values(?,?)","123","3457788");
                } catch (Exception e) {
                    status.setRollbackOnly();
                    throw new WpDaoException("数据保存出错",e);
                }
            }
        });*/

    /**
     * 批量添加
     * @param entities
     * @param <T>
     * @return
     */
    public <T> int[] batchInsert(List<T> entities){
        try {
            if(entities.size() == 0) {
                return new int[]{0} ;
            }
            String tableName = getTableName(entities.get(0).getClass());
            Field[] fields = ReflectUtil.getFields(entities.get(0).getClass());
            StringBuilder builder = new StringBuilder();
            StringBuilder columns = new StringBuilder();
            StringBuilder placeholder = new StringBuilder();
            List<Object[]> values = CollectionUtil.newArrayList();
            List<String> fieldNames = CollectionUtil.newArrayList();

            int index = 0;
            for (Field field : fields) {
                field.setAccessible(true);
                String columnName = field.getName();
                Column column = AnnotationUtil.getAnnotation(field, Column.class);
                if (column != null) {
                    columnName = column.ignore() ? null : (column.value() == null ? columnName : column.value());
                }

                if (columnName != null) {
                    if (index > 0) {
                        columns.append(",");
                        placeholder.append(",");
                    }
                    columns.append(columnName);
                    placeholder.append("?");
                    fieldNames.add(field.getName());
                    index++;
                }
            }

            for(T entity : entities){
                Object[] object = new Object[index];
                int objIndex = 0;
                for(String fieldName : fieldNames) {
                    object[objIndex++] = ReflectUtil.getFieldValue(entity,fieldName);
                }
                values.add(object);
            }

            builder.append("insert into " + tableName);
            builder.append(" (").append(columns).append(") ");
            builder.append(" values ");
            builder.append(" (").append(placeholder).append(") ");
            jdbcLog(builder.toString(),values);
            return jdbcTemplate.batchUpdate(builder.toString(),values);
        } catch (Exception ex){
            throw new WpDaoException("数据保存出错",ex);
        }
    }

    /**
     * 更新数据
     * 全部列更新
     * @param entity
     * @param <T>
     * @return
     */
    public <T> int update(T entity){
        return innerUpdate(entity,false);
    }

    public <T> int[] batchUpdate(List<T> entities){
        return innerBatchUpdate(entities,false);
    }

    /**
     * 更新
     * 值不为空的列会被更新
     * @param entity
     * @param <T>
     * @return
     */
    public <T> int updateSelective(T entity){
        return innerUpdate(entity,true);
    }

    public <T> int[] batchUpdateSelective(List<T> entities){
        return innerBatchUpdate(entities,true);
    }

    /**
     * 根据ID删除数据
     * @param id
     * @param <T>
     * @return
     */
    public <T> int deleteById(Serializable id,Class<?> clazz){
        try {
            String[] keys = getKeys(clazz);
            if(keys.length > 1){
                throw new WpDaoException("More than one primary key, Please use the delete(T entity) method");
            }
            String tableName = getTableName(clazz);
            StringBuilder builder = new StringBuilder();
            List<Object> values = CollectionUtil.newArrayList(id);

            builder.append("delete from " + tableName);
            builder.append(" where " + keys[0]  + " = ? ");
            jdbcLog(builder.toString(),values);
            return jdbcTemplate.update(builder.toString(),values.toArray());
        } catch (Exception ex){
            throw new WpDaoException("数据删除出错",ex);
        }
    }

    /**
     * 根据ID删除数据
     * @param entity
     * @param <T>
     * @return
     */
    public <T> int delete(T entity){
        try {
            String tableName = getTableName(entity.getClass());
            String[] keys = getKeys(entity.getClass());
            List<Object> values = getKeyValues(keys,entity);
            StringBuilder builder = new StringBuilder();
            StringBuilder idWhere = new StringBuilder();
            int index = 0;
            for(Object key : keys){
                if(index > 0){
                    idWhere.append(" and ");
                }
                idWhere.append(key + " = ? ");
                index++;
            }

            builder.append("delete from " + tableName);
            builder.append(" where " + idWhere);
            jdbcLog(builder.toString(),values);
            return jdbcTemplate.update(builder.toString(),values.toArray());
        } catch (Exception ex){
            throw new WpDaoException("数据删除出错",ex);
        }
    }

    /**
     * 批量删除数据实体
     * @param entities
     * @param <T>
     * @return
     */
    public <T> int batchDelete(List<T> entities){
        try {
            if(entities.size() == 0) {
                return 0;
            }
            String tableName = getTableName(entities.get(0).getClass());
            String[] keys = getKeys(entities.get(0).getClass());

            StringBuilder builder = new StringBuilder();
            StringBuilder idWhere = new StringBuilder();
            List<Object> values = CollectionUtil.newArrayList();
            int index = 0;
            for(Object key : keys){
                if(index > 0){
                    idWhere.append(" and ");
                }
                idWhere.append(keys[index] + " = ? ");
                index++;
            }

            for(T entity : entities){
                Object[] object = new Object[keys.length];
                int objIndex = 0;
                for(String key : keys) {
                   object[objIndex++] = ReflectUtil.getFieldValue(entity,key);
                }
                values.add(object);
            }

            builder.append("delete from " + tableName);
            builder.append(" where " + idWhere);
            jdbcLog(builder.toString(),values);
            return jdbcTemplate.update(builder.toString(),values.toArray());
        } catch (Exception ex){
            throw new WpDaoException("数据删除出错",ex);
        }
    }
    public <T> int batchDelete(List<T> entities,Class clazz,String tableName){
        try {
            if(entities.size() == 0) {
                return 0;
            }
            String[] keys = getKeys(clazz);

            StringBuilder builder = new StringBuilder();
            StringBuilder idWhere = new StringBuilder();
            List<Object> values = CollectionUtil.newArrayList();
            int index = 0;
            for(Object key : keys){
                if(index > 0){
                    idWhere.append(" and ");
                }
                idWhere.append(keys[index] + " = ? ");
                index++;
            }

            for(T entity : entities){
                Object[] object = new Object[keys.length];
                int objIndex = 0;
                for(String key : keys) {
                   object[objIndex++] = ReflectUtil.getFieldValue(entity,key);
                }
                values.add(object);
            }

            builder.append("delete from " + tableName);
            builder.append(" where " + idWhere);
            jdbcLog(builder.toString(),values);
            return jdbcTemplate.update(builder.toString(),values.toArray());
        } catch (Exception ex){
            throw new WpDaoException("数据删除出错",ex);
        }
    }

    /**
     * 根据ID批量删除数据
     * @param ids
     * @param <T>
     * @return
     */
    public <T> int[] deleteByIds(List<Serializable> ids,Class<?> clazz){
        try {
            String[] keys = getKeys(clazz);
            if(keys.length > 1){
                throw new WpDaoException("More than one primary key, Please use the batchDelete(List<T> entities) method");
            }
            String tableName = getTableName(clazz);
            StringBuilder builder = new StringBuilder();
            List<Object[]> values = CollectionUtil.newArrayList();

            builder.append("delete from " + tableName);
            builder.append(" where " + keys[0]  + " = ? ");

            for (Serializable id: ids) {
                values.add(new Object[]{id});
            }

            jdbcLog(builder.toString(),values);
            return jdbcTemplate.batchUpdate(builder.toString(),values);
        } catch (Exception ex){
            throw new WpDaoException("数据删除出错",ex);
        }
    }

    /**
     * 更新
     * @param entity
     * @param <T>
     * @return
     */
    private <T> int innerUpdate(T entity,boolean isSelective){
        try {
            String tableName = getTableName(entity.getClass());
            Field[] fields = ReflectUtil.getFields(entity.getClass());
            StringBuilder builder = new StringBuilder();
            StringBuilder setColumns = new StringBuilder();
            List<Object> values = CollectionUtil.newArrayList();
            String[] keys = getKeys(entity.getClass());
            List<Object> keyValues = getKeyValues(keys,entity);
            StringBuilder idWhere = new StringBuilder();

            int index = 0;
            for (Field field : fields) {
                boolean isAdd = true;
                field.setAccessible(true);
                //if(keys.contains(field.getName())) continue;

                String columnName = field.getName();
                Column column = AnnotationUtil.getAnnotation(field, Column.class);
                if (column != null) {
                    columnName = column.ignore() ? null : (column.value() == null ? columnName : column.value());
                }

                if (columnName != null) {
                    if(isSelective){
                        if(field.get(entity) == null){
                            isAdd = false;
                        }
                    }

                    if(isAdd){
                        if (index > 0) {
                            setColumns.append(",");
                        }
                        setColumns.append(columnName + " = ? ");
                        values.add(field.get(entity));
                        index++;
                    }
                }
            }

            int idIndex = 0;
            for(Object key : keys){
                if(idIndex > 0){
                    idWhere.append(" and ");
                }
                idWhere.append(key + " = ? ");
                idIndex++;
            }

            values.addAll(keyValues);
            builder.append("update " + tableName);
            builder.append(" set ").append(setColumns);
            builder.append(" where " + idWhere);
            jdbcLog(builder.toString(),values);
            return jdbcTemplate.update(builder.toString(),values.toArray());
        } catch (Exception ex){
            throw new WpDaoException("数据更新出错",ex);
        }
    }

    /**
     * 批量更新
     * @param entities
     * @param <T>
     * @return
     */
    private <T> int[] innerBatchUpdate(List<T> entities,boolean isSelective){
        try {
            if(entities.size() == 0) {
                return new int[]{0};
            }
            String tableName = getTableName(entities.get(0).getClass());
            Field[] fields = ReflectUtil.getFields(entities.get(0).getClass());
            StringBuilder builder = new StringBuilder();
            StringBuilder setColumns = new StringBuilder();
            List<Object[]> values = CollectionUtil.newArrayList();
            String[] keys = getKeys(entities.get(0).getClass());
            StringBuilder idWhere = new StringBuilder();
            List<String> fieldNames = CollectionUtil.newArrayList();

            int index = 0;
            for (Field field : fields) {
                boolean isAdd = true;
                field.setAccessible(true);
                //if(keys.contains(field.getName())) continue;

                String columnName = field.getName();
                Column column = AnnotationUtil.getAnnotation(field, Column.class);
                if (column != null) {
                    columnName = column.ignore() ? null : (column.value() == null ? columnName : column.value());
                }

                if (columnName != null) {

                    // 此处只能以第一条数据为准
                    if(isSelective){
                        if(field.get(entities.get(0)) == null){
                            isAdd = false;
                        }
                    }

                    if(isAdd){
                        if (index > 0) {
                            setColumns.append(",");
                        }
                        setColumns.append(columnName + " = ? ");
                        fieldNames.add(field.getName());
                        index++;
                    }
                }
            }

            int idIndex = 0;
            for(Object key : keys){
                if(idIndex > 0){
                    idWhere.append(" and ");
                }
                idWhere.append(key + " = ? ");
                idIndex++;
            }

            for(T entity : entities){
                Object[] object = new Object[index + keys.length];
                int objIndex = 0;
                for(String fieldName : fieldNames) {
                    object[objIndex++] = ReflectUtil.getFieldValue(entity,fieldName);
                }

                for(String key : keys) {
                    object[objIndex++] = ReflectUtil.getFieldValue(entity,key);
                }
                values.add(object);
            }

            builder.append("update " + tableName);
            builder.append(" set ").append(setColumns);
            builder.append(" where " + idWhere);
            jdbcLog(builder.toString(),values);
            return jdbcTemplate.batchUpdate(builder.toString(),values);
        } catch (Exception ex){
            throw new WpDaoException("数据更新出错",ex);
        }
    }
    public  <T> int innerUpdate(T entity,Class clazz,String tableName){
        try {
            Field[] fields = ReflectUtil.getFields(clazz);
            StringBuilder builder = new StringBuilder();
            StringBuilder setColumns = new StringBuilder();
            List<Object> values = CollectionUtil.newArrayList();
            String[] keys = getKeys(clazz);
            List<Object> keyValues = getKeyValues(keys,entity);
            StringBuilder idWhere = new StringBuilder();

            int index = 0;
            for (Field field : fields) {
                boolean isAdd = true;
                field.setAccessible(true);
                //if(keys.contains(field.getName())) continue;

                String columnName = field.getName();
                Column column = AnnotationUtil.getAnnotation(field, Column.class);
                if (column != null) {
                    columnName = column.ignore() ? null : (column.value() == null ? columnName : column.value());
                }

                if (columnName != null) {
                    if(field.get(entity) == null){
                        isAdd = false;
                    }

                    if(isAdd){
                        if (index > 0) {
                            setColumns.append(",");
                        }
                        setColumns.append(columnName + " = ? ");
                        values.add(field.get(entity));
                        index++;
                    }
                }
            }

            int idIndex = 0;
            for(Object key : keys){
                if(idIndex > 0){
                    idWhere.append(" and ");
                }
                idWhere.append(key + " = ? ");
                idIndex++;
            }

            values.addAll(keyValues);
            builder.append("update " + tableName);
            builder.append(" set ").append(setColumns);
            builder.append(" where " + idWhere);
            jdbcLog(builder.toString(),values);
            return jdbcTemplate.update(builder.toString(),values.toArray());
        } catch (Exception ex){
            throw new WpDaoException("数据更新出错",ex);
        }
    }

    public <T> List<T> selectList(String sql,Object[] params, Class<T> clazz){
        List<T> list = CollectionUtil.newArrayList();
        try {
            List<Map<String,Object>> datas = jdbcTemplate.queryForList(sql,params);

            for(Map<String,Object> data : datas){
                list.add(BeanUtil.mapToBean(data,clazz,new CopyOptions()));
            }
            jdbcLog(sql,params);
        }catch (Exception ex){
            throw new WpDaoException("数据查询出错",ex);
        }
        return list;
    }

    public <T> WpPage<T> selectList(String sql, Object[] params, WpPage page, Class<T> clazz){
        List<T> list = CollectionUtil.newArrayList();
        try {
            String totalSql = "select count(*) from (" + sql + ") _tbl";
            int total = jdbcTemplate.queryForObject(totalSql,params,Integer.class);
            jdbcLog(totalSql,params);

            int start = (page.getPage() - 1) * page.getLimit();
            String pageSql = "select * from (" + sql + ") _tbl limit " + start + "," + page.getLimit();
            List<Map<String,Object>> datas = jdbcTemplate.queryForList(pageSql,params);

            for(Map<String,Object> data : datas){
                list.add(BeanUtil.mapToBean(data,clazz,new CopyOptions()));
            }
            jdbcLog(pageSql,params);
            page.setTotal(total);
            page.setList(list);
            return page;
        }catch (Exception ex){
            throw new WpDaoException("数据查询出错",ex);
        }
    }

    public int count(String sql,Object[] params){
        int count = jdbcTemplate.queryForObject(sql,params,Integer.class);
        return count;
    }

    public <T> T selectObject(String sql,Object[] params,Class<T> clazz){
        List<T> list = this.selectList(sql,params,clazz);
        if(list.size() > 0){
            return list.get(0);
        }
        return null;
    }

    /**
     * 单列值列表查询
     * @param sql
     * @param params
     * @param clazz
     * @param <T>
     * @return
     */
    public <T> List<T> selectValueList(String sql,Object[] params,Class<T> clazz){
        return jdbcTemplate.queryForList(sql,params,clazz);
    }

    /**
     * 单列值查询
     * @param sql
     * @param params
     * @param clazz
     * @param <T>
     * @return
     */
    public <T> T selectValue(String sql,Object[] params,Class<T> clazz){
        List<T> list = this.selectValueList(sql,params,clazz);
        if(list.size() > 0){
            return list.get(0);
        }

        return null;
    }

    /**
     * 任意SQL语句执行
     * @param sql
     * @param params
     * @return
     */
    public int execute(String sql,Object[] params){
        return jdbcTemplate.update(sql,params);
    }

    /**
     * 乐观锁模式执行，仅限update语句使用
     * @param sql
     * @param params
     * @return
     *
     */
    public int executeOptimisticLock(String sql,Object[] params){
        boolean isUpdate = StrUtil.startWithIgnoreCase(sql.trim(),"update");
        if(!isUpdate){
            throw new WpDaoException("executeOptimisticLock method only allow update statement.");
        }
        // update account set balance = '"+ balance +"', version = version + 1  where id = 1 and version = '"+ version +"'"
        return jdbcTemplate.update(sql,params);
    }

    /**
     * 任意语句批量执行
     * @param sql
     * @param params
     * @return
     */
    public int[] executeBatch(String sql,List<Object[]> params){
        return jdbcTemplate.batchUpdate(sql,params);
    }

}
